<?php

namespace Fronts\Model;

use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\Sql\Sql;
use Fronts\Model\Videos;

class VideosTable extends AbstractTableGateway {

    protected $table = "tblvideos";
    protected $sql;
    protected $adapter;

    public function __construct(Adapter $adapter) {
        $this->adapter = $adapter;
        $this->resultSetPrototype = new ResultSet();
        $this->resultSetPrototype->setArrayObjectPrototype(new Videos());
        $this->initialize();
        $this->sql = new Sql($this->adapter);
    }

    public function widgetvideosnew() {
        $sqlEx = $this->sql->select();
        $sqlEx->from($this->table);
        $sqlEx->columns(array(
            "videoName" => "videoName",
            "videoLink" => "videoLink",
            "videoImgThumb" => "videoImgThumb",
            "catalogId" => "catalogId",
        ));
        $sqlEx->join("tblcatalog", "tblvideos.catalogId=tblcatalog.catalogId", array("catalogLink" => "catalogLink"));
        $sqlEx->where(array("videoActive" => "1"));
        $sqlEx->limit(5);
        $sqlEx->order("videoId DESC");
        $pst = $this->sql->prepareStatementForSqlObject($sqlEx);
        $resultSet = $pst->execute();
        $data = array();
        foreach ($resultSet as $list) {
            $data[] = $list;
        }
        return $data;
    }

    public function loadvideosindex($catalogId) {
        $sqlEx = $this->sql->select();
        $sqlEx->from($this->table);
        $sqlEx->columns(array(
            "videoName" => "videoName",
            "videoLink" => "videoLink",
            "videoImgThumb" => "videoImgThumb",
            "catalogId" => "catalogId",
        ));
        $sqlEx->join("tblcatalog", "tblvideos.catalogId=tblcatalog.catalogId", array(
            "catalogLink" => "catalogLink",
        ));
        $sqlEx->where(array(
            "videoActive" => "1",
            "tblvideos.catalogId" => $catalogId,
        ));
        $sqlEx->limit(8);
        $sqlEx->order("videoId DESC");
        $pst = $this->sql->prepareStatementForSqlObject($sqlEx);
        $resultSet = $pst->execute();
        $data = array();
        foreach ($resultSet as $list) {
            $data[] = $list;
        }
        return $data;
    }

    public function loaddetailvideos($videosLInk) {
        $sqlEx = $this->sql->select();
        $sqlEx->from($this->table);
        $sqlEx->columns(array(
            "videoName" => "videoName",
            "videoDescription" => "videoDescription",
            "videoContent" => "videoContent",
            "videoDatePost" => "videoDatePost",
            "videoName" => "videoName",
            "videosView" => "videosView",
        ));
        $sqlEx->where(array(
            "videoLink" => $videosLInk,
            "videoActive" => "1",
        ));
        $pst = $this->sql->prepareStatementForSqlObject($sqlEx);
        $resultSet = $pst->execute();
        $data = $resultSet->current();
        return $data;
    }

    public function loadvideosll($catalogId, $titlevideos) {
        $sql = "SELECT `tblvideos`.`videoName`,`tblvideos`.`videoDescription`,`tblvideos`.`videoLink`,`tblvideos`.`videoImgThumb`,`tblvideos`.`catalogId`,`tblcatalog`.`catalogLink`"
                . " FROM `tblvideos` JOIN `tblcatalog` ON `tblvideos`.`catalogId`=`tblcatalog`.`catalogId`"
                . " WHERE `tblvideos`.`catalogId`=$catalogId AND MATCH(`videoName`) AGAINST('$titlevideos')"
                . " LIMIT 16";
        $sql_result = $this->adapter->createStatement($sql)->execute();
        $results = new ResultSet();
        $data = $results->initialize($sql_result)->toArray();
        return $data;
    }

    public function checkIp($urlvideo, $Ip) {
        $sqlEx = $this->sql->select();
        $sqlEx->from("tblipvideosview");
        $sqlEx->where(array("linkvideos" => $urlvideo, "ipaddress" => $Ip));
        $pst = $this->sql->prepareStatementForSqlObject($sqlEx);
        $rs = $pst->execute();
        $check = $rs->current();
        if ($check != null) {
            return false;
        } else {
            $sqlEx2 = $this->sql->insert();
            $sqlEx2->into("tblipvideosview");
            $sqlEx2->values(array("linkvideos" => $urlvideo, "ipaddress" => $Ip));
            $pst2 = $this->sql->prepareStatementForSqlObject($sqlEx2);
            $pst2->execute();
            return true;
        }
    }

    public function countView($count, $videosLink) {
        $sqlEx = $this->sql->update();
        $sqlEx->table($this->table);
        $sqlEx->set(array("videosView" => ($count + 1)));
        $sqlEx->where(array("videoLink" => $videosLink));
        $pst = $this->sql->prepareStatementForSqlObject($sqlEx);
        $pst->execute();
    }

    public function loadpagemodel($listcata) {
        $sqlEx = $this->sql->select();
        $sqlEx->from($this->table);
        //$sqlEx->columns(array("videoId" => "videoId"));
        $sqlEx->where(array("videoActive" => "1", "catalogId" => $listcata));
        $pst = $this->sql->prepareStatementForSqlObject($sqlEx);
        $resultSet = $pst->execute();
        $data = array();
        foreach ($resultSet as $rs) {
            $data[] = $rs;
        }
        return count($data);
    }

    public function loadvideosmodel($start, $row_per, $listcatalogId) {
        $batdau = (($start - 1) * $row_per);
        $sqlEx = $this->sql->select();
        $sqlEx->from($this->table);
        $sqlEx->columns(array(
            "videoName" => "videoName",
            "videoLink" => "videoLink",
            "videoImgThumb" => "videoImgThumb",
            "catalogId" => "catalogId",
        ));
        $sqlEx->join("tblcatalog", "tblvideos.catalogId=tblcatalog.catalogId", array(
            "catalogLink" => "catalogLink",
        ));
        $sqlEx->where(array(
            "videoActive" => "1",
            "tblvideos.catalogId" => $listcatalogId,
        ));
        $sqlEx->order("videoId DESC");
        $sqlEx->limit($row_per);
        $sqlEx->offset($batdau);

        $pst = $this->sql->prepareStatementForSqlObject($sqlEx);
        $resultSet = $pst->execute();
        $data = array();
        foreach ($resultSet as $list) {
            $data[] = $list;
        }
        return $data;
    }

    public function loadsearch($start, $row_per, $key) {
        $batdau = (($start - 1) * $row_per);
        $sql = "SELECT `tblvideos`.`videoName`,`tblvideos`.`videoDescription`,`tblvideos`.`videoLink`,`tblvideos`.`videoImgThumb`,`tblvideos`.`catalogId`,`tblcatalog`.`catalogLink`"
                . " FROM `tblvideos` JOIN `tblcatalog` ON `tblvideos`.`catalogId`=`tblcatalog`.`catalogId`"
                . " WHERE MATCH(`videoName`) AGAINST('$key')"
                . " LIMIT $batdau,$row_per";
        $sql_result = $this->adapter->createStatement($sql)->execute();
        $results = new ResultSet();
        $data = $results->initialize($sql_result)->toArray();
        return $data;
    }

    public function loadpagesearchmodel($key) {
        $sql = "SELECT `tblvideos`.`videoName`,`tblvideos`.`videoDescription`,`tblvideos`.`videoLink`,`tblvideos`.`videoImgThumb`,`tblvideos`.`catalogId`,`tblcatalog`.`catalogLink`"
                . "FROM `tblvideos` JOIN `tblcatalog` ON `tblvideos`.`catalogId`=`tblcatalog`.`catalogId`"
                . "WHERE MATCH(`videoName`) AGAINST('$key')";
        $sql_result = $this->adapter->createStatement($sql, array(125000, 125200))->execute();
        $data = array();
        if ($sql_result->count() > 0) {
            $results = new ResultSet();
            $data = $results->initialize($sql_result)->toArray();
        }
        return count($data);
    }

    public function checkactivevideos($linkvideos) {
        $sqlEx = $this->sql->select();
        $sqlEx->from($this->table);
        $sqlEx->columns(array("videoId" => "videoId"));
        $sqlEx->where(array("videoActive" => "1", "videoLink" => $linkvideos));
        $pst = $this->sql->prepareStatementForSqlObject($sqlEx);
        $resultset = $pst->execute();
        $rs = $resultset->current();
        if ($rs != null) {
            return true;
        } else {
            return false;
        }
    }

}
